package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import main.Config;

public class DatabaseAPI {
	private Connection connection = null;
	private Config config = null;
	
	public DatabaseAPI(Config config) {
		this.config = config;
	}
	
	public Connection getConnection() {
		return connection;
	}
	
	public void openDatabase() {
		try{
			//Load the JDBC Driver
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Driver loaded");
			
			//Establish a connection
			String connString = config.getDbAddress();
			
			System.out.println("Database connected: " + connString);
			connection = DriverManager.getConnection
					(connString, config.getDbUserName(), config.getDbPassword());
			
			System.out.println("Statement created");
			//Statement statement = connection.createStatement();
			//statement.execute("SET GLOBAL character_set_database = utf8");
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when opened: ");
			ex.printStackTrace();
		}
		catch(ClassNotFoundException ex) { 
		    System.out.println("Driver not found"); 
		    ex.printStackTrace();
		}
	}
	
	public void closeDatabase() {
		try{
			connection.close();
			System.out.println("database disconnected");
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when closed");
		}
	}
	
	/**
	 * create new user
	 * @param username
	 * @param password
	 * @return
	 */
	public boolean newUser(String username, String password) {
		//the username is existed
		if(checkExistUser(username)){
			return false;
		}			
		System.out.print("new user");
		String sql = "INSERT INTO users values (null,'" + username + "','" + password + "','0',0);";
		
		try {
			System.out.println(sql);
			Statement statement = connection.createStatement();
			statement.execute(sql);
			
			System.out.println("database: user create success! -----> " + sql);
			return true;
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when insert new explanation");
			ex.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Check existing user in the database
	 * @param username
	 * @return
	 */
	public boolean checkExistUser(String username){
		String sql = "SELECT * FROM users where username='" + username + "';";
		System.out.print("check existing user");
		try {
			System.out.println("CheckExistUser: "+sql);
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			if (set.next()){
				System.out.println("user: " + set.getString("userID") +" is existed");
				return true;
			}
			
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when printing all record");
			ex.printStackTrace();
		}
		System.out.println("Not such user: " + username);
		return false;
	}
	
	/**
	 * check whether a username and password is valid
	 * @param username
	 * @param password
	 * @return
	 */
	public boolean checkValid(String username, String password){
		String sql = "SELECT * FROM users where username='" + username + "' and password='" + password + "';";
		System.out.print("user login");
		try {
			System.out.println(sql);
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			if (set.next()){
				System.out.println("where come!" + set.getString("userID"));
				return true;
			}
			
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when printing all record");
			ex.printStackTrace();
		}
		System.out.println("Not such user: " + username);
		return false;
	}
	
	/**
	 * get userID
	 * @param username
	 * @param password
	 * @return
	 */
	public String getUserID(String username, String password){
		String sql = "SELECT * FROM users where username='" + username + "' and password='" + password + "';";
		System.out.print("get user id");
		try {
			System.out.println("get userID: "+sql);
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			if (set.next()){
				System.out.println("where come!" + set.getString("userID"));
				return set.getString("userID");
			}
			
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when printing all record");
			ex.printStackTrace();
		}
		System.out.println("Not such user: " + username);
		return "";
	}
	
	/**
	 * updateScore
	 * @param userID
	 * @param isWin
	 * @return
	 */
	public boolean updateScore(int userID, boolean isWin){
		String sql = "SELECT * FROM users where userID=" + userID + ";";
		int played = 0;
		int won = 0;
		
		try {
			//System.out.println(sql);
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			if (!set.next()){
				return false;
			}
			
			played = set.getInt("played");
			won = set.getInt("won");
			
			played ++;
			if (isWin){
				won ++;
			}
			
			sql = "UPDATE users set played=" + played + ", won=" + won + " where userID=" + userID + ";";
			statement = connection.createStatement();
			statement.execute(sql);
			System.out.println("database: user "+userID+" score update success! -----> " + sql);
			return true;
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when printing all record");
			ex.printStackTrace();
			return false;
		}
		
	}
	
	/**
	 * get player played
	 * @param userID
	 * @return
	 */
	public String getPlayed(int userID){
		String sql = "SELECT * FROM users where userID='" + userID + "';";
		System.out.print("get user played");
		try {
			System.out.println("get user played: "+sql);
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			if (set.next()){
				System.out.println(userID+" played: "+set.getString("played"));
				return set.getString("played");
			}
			
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when printing all record");
			ex.printStackTrace();
		}
		System.out.println("Not such user: " + userID);
		return "";
	}
	
	/**
	 * get player won
	 * @param userID
	 * @return
	 */
	public String getWon(int userID){
		String sql = "SELECT * FROM users where userID='" + userID + "';";
		System.out.print("get user won");
		try {
			System.out.println("get user won: "+sql);
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			if (set.next()){
				System.out.println(userID+" won: "+set.getString("won"));
				return set.getString("won");
			}
			
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when printing all record");
			ex.printStackTrace();
		}
		System.out.println("Not such user: " + userID);
		return "";
	}
	

	/**
	 * show all users
	 */
	public void printRecords(){
		String sql = "SELECT * FROM users";
		
		try {
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			System.out.println("userID\tusername\tpassword\tplayed\twon");
			while(set.next()) {
				String userID = set.getString("userID");
				String username = set.getString("username");
				String password = set.getString("password");
				int played = set.getInt("played");
				int won = set.getInt("won");
				System.out.println(userID+"\t"+username+"\t"+password+"\t"+played+"\t"+won);
			}
			
		}
		catch(SQLException ex) {
			System.out.println("SQL Exception when printing all record");
			ex.printStackTrace();
		}
	}
	

}
